Using External Variables with Text Box Control

The following example code shows how to create and use external variables with the standard textbox control.

In the example:

There are two external variables : "x" and "y" and two textbox controls : "textBox1" and "textBox2"
Cell B1 and textBox1 will provide the arguments for the formula. The arguments can be modified to display the updated result.
The result will be displayed in textBox2.

C#	Copy Code
// Creating and defining the external variable public class TextBoxExternalVariable : ExternalVariable { private TextBox _textBox; private bool _asInput; public TextBoxExternalVariable(TextBox textBox, bool asInput) { _textBox = textBox; if (asInput) { textBox.TextChanged += TextBox_TextChanged; } } private void TextBox_TextChanged(object sender, EventArgs e) { Dirty(); } protected override bool OnDirtying() { return !_asInput; } protected override void OnDirtied() { Refresh(); } protected override void EvaluateCore(IEvaluationContext context, IValue result) { string text = _textBox.Text; if (!string.IsNullOrEmpty(text) && double.TryParse(text, out double dblValue)) { result.SetValue(dblValue); } else { result.SetValue(text); } } public void Refresh() { if (!_asInput) { _textBox.Text = this.Value.GetText(); } } } // Using the external variable with text box control private void Form2_Load(object sender, EventArgs e) { var workbook = fpSpread1.AsWorkbook(); var activeSheet = workbook.ActiveSheet; activeSheet.Cells["A1"].Value = "Factor"; activeSheet.Cells["B1"].Value = 2; // Adding Ext. Variable - x referring to textbox1 workbook.Names.AddExternalVariable("x", new TextBoxExternalVariable(textBox1, true)); // Adding Ext. Variable "y" referring to textbox2 with formula "Sheet1!B1 * x" var extVariable2 = new TextBoxExternalVariable(textBox2, false); workbook.Names.AddExternalVariable("y", extVariable2, "Sheet1!B1 * x"); extVariable2.Refresh(); }

Copy Code

// Creating and defining the external variable
public class TextBoxExternalVariable : ExternalVariable
{
    private TextBox _textBox;
    private bool _asInput;
    public TextBoxExternalVariable(TextBox textBox, bool asInput)
    {
        _textBox = textBox;
        if (asInput)
        {
            textBox.TextChanged += TextBox_TextChanged;
        }
    }
    private void TextBox_TextChanged(object sender, EventArgs e)
    {
        Dirty();
    }
   
    protected override bool OnDirtying()
    {
        return !_asInput;
    }
  
    protected override void OnDirtied()
    {
        Refresh();
    }
   
    protected override void EvaluateCore(IEvaluationContext context,
     IValue result)
    {
        string text = _textBox.Text;
        if (!string.IsNullOrEmpty(text) && double.TryParse(text,
             out double dblValue))
        {
            result.SetValue(dblValue);
        }
        else
        {
            result.SetValue(text);
        }
    }
   
    public void Refresh()
    {
        if (!_asInput)
        {
            _textBox.Text = this.Value.GetText();
        }
    }
} 
// Using the external variable with text box control
private void Form2_Load(object sender, EventArgs e)
{
 var workbook = fpSpread1.AsWorkbook();
 var activeSheet = workbook.ActiveSheet;
 activeSheet.Cells["A1"].Value = "Factor";
 activeSheet.Cells["B1"].Value = 2;
   
 // Adding Ext. Variable - x referring to textbox1
 workbook.Names.AddExternalVariable("x",
 new TextBoxExternalVariable(textBox1, true));
   
 // Adding Ext. Variable "y" referring to textbox2 with formula "Sheet1!B1 * x"
 var extVariable2 = new TextBoxExternalVariable(textBox2, false);
 workbook.Names.AddExternalVariable("y", extVariable2, "Sheet1!B1 * x");
 extVariable2.Refresh();
}

VB	Copy Code
'Creating and defining the external variable Public Class TextBoxExternalVariable Inherits ExternalVariable Private _textBox As TextBox Private _asInput As Boolean Public Sub New(ByVal textBox As TextBox, ByVal asInput As Boolean) _textBox = textBox If asInput Then textBox.TextChanged += AddressOf TextBox_TextChanged End If End Sub Private Sub TextBox_TextChanged(ByVal sender As Object, ByVal e As EventArgs) Dirty() End Sub Protected Overrides Function OnDirtying() As Boolean Return Not _asInput End Function Protected Overrides Sub OnDirtied() Refresh() End Sub Protected Overrides Sub EvaluateCore(ByVal context As IEvaluationContext, ByVal result As IValue) Dim text As String = _textBox.Text Dim dblValue As Double = Nothing If Not String.IsNullOrEmpty(text) AndAlso Double.TryParse(text, dblValue) Then result.SetValue(dblValue) Else result.SetValue(text) End If End Sub Public Sub Refresh() If Not _asInput Then _textBox.Text = Me.Value.GetText() End If End Sub End Class 'Using the external variable with text box control Private Sub Form2_Load(ByVal sender As Object, ByVal e As EventArgs) Dim workbook = fpSpread1.AsWorkbook() Dim activeSheet = workbook.ActiveSheet activeSheet.Cells("A1").Value = "Factor" activeSheet.Cells("B1").Value = 2 workbook.Names.AddExternalVariable("x", New TextBoxExternalVariable(textBox1, True)) Dim extVariable2 = New TextBoxExternalVariable(textBox2, False) workbook.Names.AddExternalVariable("y", extVariable2, "Sheet1!B1 * x") extVariable2.Refresh() End Sub

Copy Code

'Creating and defining the external variable
Public Class TextBoxExternalVariable Inherits ExternalVariable
Private _textBox As TextBox
Private _asInput As Boolean
Public Sub New(ByVal textBox As TextBox, ByVal asInput As Boolean)
_textBox = textBox
If asInput Then
textBox.TextChanged += AddressOf TextBox_TextChanged
End If
End Sub
Private Sub TextBox_TextChanged(ByVal sender As Object, ByVal e As EventArgs)
Dirty()
End Sub
Protected Overrides Function OnDirtying() As Boolean
Return Not _asInput
End Function
Protected Overrides Sub OnDirtied()
Refresh()
End Sub
Protected Overrides Sub EvaluateCore(ByVal context As IEvaluationContext, ByVal result As IValue)
Dim text As String = _textBox.Text
Dim dblValue As Double = Nothing
If Not String.IsNullOrEmpty(text) AndAlso Double.TryParse(text, dblValue) Then
result.SetValue(dblValue)
Else
result.SetValue(text)
End If
End Sub
Public Sub Refresh()
If Not _asInput Then
_textBox.Text = Me.Value.GetText()
End If
End Sub
End Class    

'Using the external variable with text box control
Private Sub Form2_Load(ByVal sender As Object, ByVal e As EventArgs)
Dim workbook = fpSpread1.AsWorkbook()
Dim activeSheet = workbook.ActiveSheet
activeSheet.Cells("A1").Value = "Factor"
activeSheet.Cells("B1").Value = 2
workbook.Names.AddExternalVariable("x", New TextBoxExternalVariable(textBox1, True))
Dim extVariable2 = New TextBoxExternalVariable(textBox2, False)
workbook.Names.AddExternalVariable("y", extVariable2, "Sheet1!B1 * x")
extVariable2.Refresh()
End Sub